package com.group.zsxm.service;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.springframework.stereotype.Service;

import com.fins.gt.model.FilterInfo;
import com.fins.gt.model.SortInfo;
import com.group.core.common.LimitPage;
import com.group.core.common.Page;
import com.group.zsxm.entity.XtDict;
import com.group.zsxm.entity.XtDlb;
import com.group.zsxm.exception.BusException;
import com.group.zsxm.service.common.BaseService;

@Service
public class JfhsbService extends BaseService{
	

	public Object getShzfList(LimitPage limit, Map<String, String> parMap, SortInfo sortInfo, List<FilterInfo> filterInfos,String dwid){
		String name = parMap.get("name") == null ? "" :  parMap.get("name");
		String where = parMap.get("where") == null ? "" :  parMap.get("where");
		
		Map jfhsb = new HashMap();
		String sql = " select count(*) from jfhsb a where a.bz=1 and len(dm)=4 ";
		if(this.getSimpleJdbcTemplate().queryForInt(sql) > 0){
			sql = " select * from jfhsb a where a.bz=1 and len(dm)=4 ";
			jfhsb = this.getSimpleJdbcTemplate().queryForMap(sql);
		}else{
			return  new Page();
		}
		String dm = (jfhsb.get("dm")==null?"":String.valueOf(jfhsb.get("dm")));
		
		String ls_sql = "" ;
		ls_sql = "select a.RYID,a.XM,a.SFZ,a.SEX_MC,a.BIRTHDAY,a.XL_MC,a.ZC_MC,a.ZJZ,b.DWID as DC,b.SHZZBZ, b.SHZCSJ, b.SHZZZE, b.ZFZFD, b.ZFBTBZ, b.ZFZCSJ, b.ZFBTZE," +
				"b.ZFZFD2, b.ZFBTBZ2, b.ZFZCSJ2, b.ZFBTZE2, b.ZE, b.SBXX, b.BZ, b.HTYY from dw_ryxx_v a left join " +
				" ( select * from jfhsb_shzf where sbdm='"+dm+"0001' and dwid='"+dwid+"' ) b on a.ryid=b.ryid " +
						" where 1=1 and  a.dwid='"+dwid+"' and a.ryid in (select distinct ryid from DW_RYXX_JFHTYPE where dwid="+dwid+" and jfhtype in (1,2)) ";
		if(where != null && !where.equals("")){
			ls_sql += " and upper(a."+where+") like '%" + name.toUpperCase() + "%'";
		}
		
		String sortFieldName = "";
		if(sortInfo != null ){
			sortFieldName = sortInfo.getFieldName();
		}
		String sortCond = getGridSortString(sortInfo);
		String filterCond = getGridFilterString(filterInfos);
		if (filterCond != null) {
			ls_sql += filterCond;
		}
		if (sortCond != null ) {
			if(sortFieldName.equals("B_NL")){
				ls_sql += " order by a.birthday " ;
			}else{
				ls_sql += " order by " + sortCond.split("_")[0];
			}
		} else {
			ls_sql += " order by a.ryid desc ";
		}
		if (limit != null) {
			return this.queryForListWithPage(ls_sql, limit);
		} else {
			return this.simpleJdbcTemplate.queryForList(ls_sql);
		}
	}
	
	

	public Object getAjfList(LimitPage limit, Map<String, String> parMap, SortInfo sortInfo, List<FilterInfo> filterInfos,String dwid){
		String name = parMap.get("name") == null ? "" :  parMap.get("name");
		String where = parMap.get("where") == null ? "" :  parMap.get("where");
		
		Map jfhsb = new HashMap();
		String sql = " select count(*) from jfhsb a where a.bz=1 and len(dm)=4 ";
		if(this.getSimpleJdbcTemplate().queryForInt(sql) > 0){
			sql = " select * from jfhsb a where a.bz=1 and len(dm)=4 ";
			jfhsb = this.getSimpleJdbcTemplate().queryForMap(sql);
		}else{
			return  new Page();
		}
		String dm = (jfhsb.get("dm")==null?"":String.valueOf(jfhsb.get("dm")));
		
		String ls_sql = "" ;
		ls_sql = "select a.*,b.DWID as DC,convert(varchar,GFSJ,23) GFSJ, ZZBZ, GFDD, FWMJ, FWZE," +
				"convert(varchar,JFSJ,23) JFSJ, DYNBT, BZ, HTYY" +
				" from dw_ryxx_v a left join " +
		" ( select * from jfhsb_ajf where sbdm='"+dm+"0002' and dwid='"+dwid+"' ) b on a.ryid=b.ryid " +
				" where 1=1 and  a.dwid='"+dwid+"'  and a.ryid in (select distinct ryid from DW_RYXX_JFHTYPE where dwid="+dwid+" and jfhtype in (3))  ";
		
		
		if(where != null && !where.equals("")){
			ls_sql += " and upper("+where+") like '%" + name.toUpperCase() + "%'";
		}
		
		String sortFieldName = "";
		if(sortInfo != null ){
			sortFieldName = sortInfo.getFieldName();
		}
		String sortCond = getGridSortString(sortInfo);
		String filterCond = getGridFilterString(filterInfos);
		if (filterCond != null) {
			ls_sql += filterCond;
		}
		if (sortCond != null ) {
			if(sortFieldName.equals("B_NL")){
				ls_sql += " order by birthday " ;
			}else{
				ls_sql += " order by " + sortCond.split("_")[0];
			}
		} else {
			ls_sql += " order by a.ryid desc ";
		}
		if (limit != null) {
			return this.queryForListWithPage(ls_sql, limit);
		} else {
			return this.simpleJdbcTemplate.queryForList(ls_sql);
		}
	}
	
	
	/**
	 * 根据LBID 获取
	 * @param id
	 * @return
	 */
	public Map<Integer, List<XtDict>> getDictListByLbid(String id){
		Map<Integer, List<XtDict>> ms = new HashMap();
		List<XtDict> xtdicts = new ArrayList();
		int lbid = 0;
		String sql_q = " select * from xt_dlb where lbframe!=1 and lbid="+id;
		List<XtDlb> xtdlbs = this.getSimpleJdbcTemplate().query(sql_q, resultBeanMapper(XtDlb.class));
		for(int i=0;i<xtdlbs.size();i++){
			lbid = xtdlbs.get(i).getLbid();
			ms.put(lbid, this.getSimpleJdbcTemplate().query(" select dictbh,dictname from xt_dict where lbid="+lbid+" order by dictbh", resultBeanMapper(XtDict.class)));
			
		}
		return ms;
	}
	
	public Map getJfhsbJbxxWithShzf(String dwid){
		Map m = new HashMap();
		String sql = " select count(*) from jfhsb a where a.bz=1 and len(dm)=4 ";
		if(this.getSimpleJdbcTemplate().queryForInt(sql) > 0){
			sql = " select * from jfhsb a where a.bz=1 and len(dm)=4 ";
			m = this.getSimpleJdbcTemplate().queryForMap(sql);
		}
		return m;
	}
	
	/**
	 * 检查是否上传了相关的人员附件信息
	 * @param dwid
	 * @param ryid
	 * @param type 1:生活资助和住房补助  2：安家费
	 */
	public boolean checkFjInfo(String dwid,String ryid,String type){
		String sql = "";
		Integer count = 0;
		if(Integer.parseInt(type) == 1){
			sql = "select count(*) from dw_ryxx_fj where dwid="+dwid+" and ryid="+ryid+" and type="+type+" and tt =1 and xx in (1,2,3) and shbz=2";
			count = this.getSimpleJdbcTemplate().queryForInt(sql);
			if(count > 0){
				sql = "select count(*) from dw_ryxx_fj where dwid="+dwid+" and ryid="+ryid+" and type="+type+" and tt =2 and xx in (1,2,3) and shbz=2";
				count = this.getSimpleJdbcTemplate().queryForInt(sql);
				if(count > 0 ){
					return true;
				}else{
					throw new BusException("附件资料上传不全,不能申报!");
				}
			}else{
				throw new BusException("附件资料上传不全,不能申报!");
			}
		}else{
			sql = "select count(*) from dw_ryxx_fj where dwid="+dwid+" and ryid="+ryid+" and type="+type+" and tt =1 and xx in (1,2) and shbz=2";
			count = this.getSimpleJdbcTemplate().queryForInt(sql);
			if(count >= 2){
				sql = "select count(*) from dw_ryxx_fj where dwid="+dwid+" and ryid="+ryid+" and type="+type+" and tt =2 and xx in (1,2) and shbz=2";
				count = this.getSimpleJdbcTemplate().queryForInt(sql);
				if(count > 0 ){
					return true;
				}else{
					throw new BusException("附件资料上传不全,不能申报!");
				}
			}else{
				throw new BusException("附件资料上传不全,不能申报!");
			}
		}
	}
	
	public Map preJfhshzf(String dwid,String ryid){
		String sql = " select count(*) from jfhsb a where a.bz=1 and len(dm)=4 ";
		String dm = "";
		if(this.getSimpleJdbcTemplate().queryForInt(sql) > 0){
			sql = " select dm from jfhsb a where a.bz=1 and len(dm)=4 ";
			dm = this.getSimpleJdbcTemplate().queryForObject(sql,String.class);
		}else{
			return  new HashMap();
		}
		//String dm = (jfhsb.get("dm")==null?"":String.valueOf(jfhsb.get("dm")));
		
		sql = " select count(*) from jfhsb_shzf where dwid="+dwid+" and ryid="+ryid+" and sbdm='"+dm+"0001'";
		if(this.getSimpleJdbcTemplate().queryForInt(sql) > 0){
			sql = " select " +
			"SBID, SBDM, DWID, RYID,convert(numeric(20), SHZZBZ) SHZZBZ, " +
			"convert(numeric(20,1),SHZCSJ ) SHZCSJ, SHZZZE, ZFZFD, convert(numeric(20), ZFBTBZ) ZFBTBZ," +
			"convert(numeric(20,1),ZFZCSJ) ZFZCSJ , ZFBTZE, ZE, SM, SBXX, BZ, HTYY, ZFZFD2, convert(numeric(20,0),ZFBTBZ2) ZFBTBZ2, convert(numeric(20,1),ZFZCSJ2) ZFZCSJ2 , ZFBTZE2" +
			
			" from jfhsb_shzf where dwid="+dwid+" and ryid="+ryid+" and sbdm='"+dm+"0001'";
			return this.getSimpleJdbcTemplate().queryForMap(sql);
		}else{
			return new HashMap();
		}
	}
	
	public int doSaveJfhshzf(String dwid,Map<String,String> m,String ryid){
		String sql = "";
		String sbid = m.get("sbid");
		Map jfhsb = new HashMap();
		
		
		sql = " select * from jfhsb a where a.bz=1 and len(dm)=4 ";
		jfhsb = this.getSimpleJdbcTemplate().queryForMap(sql);
		if(sbid != null && !sbid.equals("")){
			sql = " select isnull(bz,0) from jfhsb_shzf where sbid='"+sbid+"' ";
			Integer bz = this.getSimpleJdbcTemplate().queryForInt(sql);
			if( bz !=0 && bz != 4){
				throw new BusException("当前数据已上报，不能修改！");
			}
			
			sql = " delete from jfhsb_shzf where sbid="+sbid;
			this.getSimpleJdbcTemplate().update(sql);
			
			sql = " insert into jfhsb_shzf(SBID,ryid,SBDM,DWID," +
					"SHZZBZ,SHZCSJ,SHZZZE,ZFZFD,ZFBTBZ,ZFZCSJ,ZFBTZE,ZFZFD2,ZFBTBZ2,ZFZCSJ2,ZFBTZE2,ZE,SM" +
			",SBXX,BZ) " +
			"values('"+sbid+"',"+ryid+",'"+jfhsb.get("dm")+"0001','"+dwid+"','"+transToN(m.get("shzzbz"))+"'," +
					"'"+transToN(m.get("shzcsj"))+"','"+transToN(m.get("shzzze"))+"','"+transToN(m.get("zfzfd"))+"','"+transToN(m.get("zfbtbz"))+"'," +
					"'"+transToN(m.get("zfzcsj"))+"','"+transToN(m.get("zfbtze"))+"','"+transToN(m.get("zfzfd2"))+"','"+transToN(m.get("zfbtbz2"))+"'," +
					"'"+transToN(m.get("zfzcsj2"))+"','"+transToN(m.get("zfbtze2"))+"','"+transToN(m.get("ze"))+"','"+m.get("sm")+"'," +
					"'"+m.get("sbxx")+"',0)";
			this.getSimpleJdbcTemplate().update(sql);
		
		}else{

			
			sbid =  String.valueOf(this.getMaxKey("JFHSB_SHZF"));
			
			sql = " insert into jfhsb_shzf(SBID,ryid,SBDM,DWID," +
					"SHZZBZ,SHZCSJ,SHZZZE,ZFZFD,ZFBTBZ,ZFZCSJ,ZFBTZE,ZFZFD2,ZFBTBZ2,ZFZCSJ2,ZFBTZE2,ZE,SM" +
				",SBXX,BZ) " +
				"values('"+sbid+"',"+ryid+",'"+jfhsb.get("dm")+"0001','"+dwid+"','"+transToN(m.get("shzzbz"))+"'," +
						"'"+transToN(m.get("shzcsj"))+"','"+transToN(m.get("shzzze"))+"','"+transToN(m.get("zfzfd"))+"','"+transToN(m.get("zfbtbz"))+"'," +
						"'"+transToN(m.get("zfzcsj"))+"','"+transToN(m.get("zfbtze"))+"','"+transToN(m.get("zfzfd2"))+"','"+transToN(m.get("zfbtbz2"))+"'," +
					"'"+transToN(m.get("zfzcsj2"))+"','"+transToN(m.get("zfbtze2"))+"','"+transToN(m.get("ze"))+"','"+m.get("sm")+"'," +
						"'"+m.get("sbxx")+"',0)";
			this.getSimpleJdbcTemplate().update(sql);
		}
		return 1;
	}
	
	
	public void doJfhshzfD(String dwid,String ryid){
		String sql = " select count(*) from jfhsb a where a.bz=1 and len(dm)=4 ";
		String dm = "";
		if(this.getSimpleJdbcTemplate().queryForInt(sql) > 0){
			sql = " select dm from jfhsb a where a.bz=1 and len(dm)=4 ";
			dm = this.getSimpleJdbcTemplate().queryForObject(sql,String.class);
		}else{
			throw new BusException("当前数据不存在！");
		}
		
		sql = " select count(*) from jfhsb_shzf where dwid='"+dwid+"' and ryid in ("+ryid+") and bz in (3,5) and sbdm='"+dm+"0001'";
		
		if( this.getSimpleJdbcTemplate().queryForInt(sql) > 0){
			throw new BusException("当前数据已上报，不能删除！");
		}
		
		sql = " delete from jfhsb_shzf where  dwid='"+dwid+"' and ryid in ("+ryid+") and sbdm='"+dm+"0001'";
		this.getSimpleJdbcTemplate().update(sql);
	}
	
	
	public int doSaveJfhaj(String dwid,Map<String,String> m,String ryid){
		String sql = "";
		String sbid = m.get("sbid");
		Map jfhsb = new HashMap();
		sql = " select * from jfhsb a where a.bz=1 and len(dm)=4 ";
		jfhsb = this.getSimpleJdbcTemplate().queryForMap(sql);
		if(sbid != null && !sbid.equals("")){
			sql = " select isnull(bz,0) from jfhsb_ajf where sbid='"+sbid+"' ";
			Integer bz = this.getSimpleJdbcTemplate().queryForInt(sql);
			if( bz !=0 && bz != 4){
				throw new BusException("当前数据已上报，不能修改！");
			}
			
			sql = " delete from jfhsb_ajf where sbid="+sbid;
			this.getSimpleJdbcTemplate().update(sql);
			
			sql = " insert into jfhsb_ajf(SBID,RYID,SBDM,DWID,GFSJ,ZZBZ,GFDD,FWMJ,FWZE,JFSJ,DYNBT,SM,BZ) " +
				"values('"+sbid+"',"+ryid+",'"+jfhsb.get("dm")+"0002','"+dwid+"',"+transToD(m.get("gfsj"))+"," +
				"'"+transToN(m.get("zzbz"))+"','"+m.get("gfdd")+"','"+transToN(m.get("fwmj"))+"','"+transToN(m.get("fwze"))+"'," +
				""+transToD(m.get("jfsj"))+",'"+transToN(m.get("dynbt"))+"','"+m.get("sm")+"',0)";
			this.getSimpleJdbcTemplate().update(sql);
		
		}else{

			
			sbid =  String.valueOf(this.getMaxKey("JFHSB_AJF"));
			
			sql = " insert into jfhsb_ajf(SBID,RYID,SBDM,DWID,GFSJ,ZZBZ,GFDD,FWMJ,FWZE,JFSJ,DYNBT,SM,BZ) " +
					"values('"+sbid+"',"+ryid+",'"+jfhsb.get("dm")+"0002','"+dwid+"',"+transToD(m.get("gfsj"))+"," +
					"'"+transToN(m.get("zzbz"))+"','"+m.get("gfdd")+"','"+transToN(m.get("fwmj"))+"','"+transToN(m.get("fwze"))+"'," +
					""+transToD(m.get("jfsj"))+",'"+transToN(m.get("dynbt"))+"','"+m.get("sm")+"',0)";
			this.getSimpleJdbcTemplate().update(sql);
		}
		return 1;
	}
	
	/**
	 * 上报
	 * @param sbids
	 * @return
	 */
	public int doJfhShzfBySb(String dwid,String ryid){
		String sql = " select count(*) from jfhsb a where a.bz=1 and len(dm)=4 ";
		if(this.getSimpleJdbcTemplate().queryForInt(sql) == 0){
			throw new BusException("没有相应启用的申报！");
		}
		
		sql = " select * from jfhsb a where a.bz=1 and len(dm)=4 ";
		Map jfhsb = this.getSimpleJdbcTemplate().queryForMap(sql);
		
		sql = " select count(*) from jfhsb_shzf where dwid="+dwid+" and sbdm='"+jfhsb.get("dm")+"0001' and (bz=3 or bz=5) and ryid in ("+ryid+")";
		if(this.getSimpleJdbcTemplate().queryForInt(sql) > 0){
			throw new BusException("有相应的数据已提交 或 审核通过");
		}
		
		sql = " select count(*) from jfhsb_shzf where dwid="+dwid+" and sbdm='"+jfhsb.get("dm")+"0001'  and ryid in ("+ryid+") ";
		if(this.getSimpleJdbcTemplate().queryForInt(sql) == 0){
			throw new BusException("没有相应的申报数据需要提交!");
		}
		
		sql = " update jfhsb_shzf set bz=5 where dwid="+dwid+" and sbdm='"+jfhsb.get("dm")+"0001'  and ryid in ("+ryid+") ";
		this.getSimpleJdbcTemplate().update(sql);
		return 1;
	}
	
	/**
	 * 安家上报
	 * @param sbids
	 * @return
	 */
	public int doJfhAjfBySb(String dwid,String ryid){
		String sql = " select count(*) from jfhsb a where a.bz=1 and len(dm)=4 ";
		if(this.getSimpleJdbcTemplate().queryForInt(sql) == 0){
			throw new BusException("没有相应启用的申报！");
		}
		

		sql = " select * from jfhsb a where a.bz=1 and len(dm)=4 ";
		Map jfhsb = this.getSimpleJdbcTemplate().queryForMap(sql);
		

		sql = " select count(*) from jfhsb_ajf where dwid="+dwid+" and sbdm='"+jfhsb.get("dm")+"0002' and (bz=3 or bz=5)  and ryid in ("+ryid+") ";
		if(this.getSimpleJdbcTemplate().queryForInt(sql) > 0){
			throw new BusException("有相应的数据已提交 或 审核通过");
		}
		
		sql = " select count(*) from jfhsb_ajf where dwid="+dwid+" and sbdm='"+jfhsb.get("dm")+"0002'   and ryid in ("+ryid+") ";
		if(this.getSimpleJdbcTemplate().queryForInt(sql) == 0){
			throw new BusException("没有相应的申报数据需要提交!");
		}

		sql = " update jfhsb_ajf set bz=5 where dwid="+dwid+" and sbdm='"+jfhsb.get("dm")+"0002'  and ryid in ("+ryid+") ";
		this.getSimpleJdbcTemplate().update(sql);
		return 1;
	}
	
	public Map preJfhAjf(String dwid,String ryid){
		String sql = " select count(*) from jfhsb a where a.bz=1 and len(dm)=4 ";
		String dm = "";
		if(this.getSimpleJdbcTemplate().queryForInt(sql) > 0){
			sql = " select dm from jfhsb a where a.bz=1 and len(dm)=4 ";
			dm = this.getSimpleJdbcTemplate().queryForObject(sql,String.class);
		}else{
			return  new HashMap();
		}
		
		sql = " select count(*) from jfhsb_ajf where dwid="+dwid+" and ryid="+ryid+" and sbdm='"+dm+"0002'";
		if(this.getSimpleJdbcTemplate().queryForInt(sql) > 0){
			sql = " select SBID, SBDM, DWID, RYID, convert(varchar,GFSJ,23) GFSJ , ZZBZ, GFDD, FWMJ, " +
					"FWZE,convert(varchar,JFSJ,23) JFSJ, DYNBT, SM, BZ, HTYY from jfhsb_ajf where dwid="+dwid+" and ryid="+ryid+" and sbdm='"+dm+"0002'";
			return this.getSimpleJdbcTemplate().queryForMap(sql);
		}else{
			return new HashMap();
		}
	}
	
	public void doJfhajfD(String dwid,String ryid){
		String sql = " select count(*) from jfhsb a where a.bz=1 and len(dm)=4 ";
		String dm = "";
		if(this.getSimpleJdbcTemplate().queryForInt(sql) > 0){
			sql = " select dm from jfhsb a where a.bz=1 and len(dm)=4 ";
			dm = this.getSimpleJdbcTemplate().queryForObject(sql,String.class);
		}else{
			throw new BusException("当前没有数据！");
		}
		
		
		sql = " select count(*) from jfhsb_ajf where dwid='"+dwid+"' and ryid in ("+ryid+") and bz in (3,5) and sbdm='"+dm+"0002'";
		
		if( this.getSimpleJdbcTemplate().queryForInt(sql) > 0){
			throw new BusException("当前数据已上报，不能删除！");
		}
		
		sql = " delete from jfhsb_ajf where  dwid='"+dwid+"' and ryid in ("+ryid+") and sbdm='"+dm+"0002'";
		this.getSimpleJdbcTemplate().update(sql);
	}
	
	public int getJfhType(String dwid,String ryid,String v){
		String sql = " select count(*) from DW_RYXX_JFHTYPE where dwid="+dwid+" and ryid="+ryid+" and JFHTYPE="+v;
		return this.getSimpleJdbcTemplate().queryForInt(sql);
	}
}
